<?php

if (!defined('BASEPATH'))
	exit('No direct script access allowed');

class Laporan_setoran_model extends CI_Model {

	function __construct() {
		parent::__construct();
	}

	function get_laporan_nopol($tgl_awal, $tgl_akhir) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->group_by('NoPol');
		$this->db->select('NoPol,COUNT(NoPol) AS jumlah_nopol');
		$this->db->where('tanggal BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_laporan_nopol_by_cabang($tgl_awal, $tgl_akhir, $cabang) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->group_by('NoPol');
		$this->db->select('NoPol,COUNT(NoPol) AS jumlah_nopol');
		$this->db->where('tanggal BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		if ($cabang != 'SEMUA')
			$this->db->where('cabang_keberangkatan', $cabang);
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_setoran_by_nopol($nopol) {
		$this->db->select("SUM(total_penumpang) AS total_penumpang,SUM(tiket_void) AS tiket_void,SUM(total_omset) AS total_omset,
                SUM(total_discount) AS total_discount,SUM(fee_agen) AS fee_agen,SUM(total_void) AS total_void, SUM(jumlah) AS jumlah,
                SUM(total_setoran) AS total_setoran, SUM(total_laba) AS total_laba");
		$this->db->join('TblBiaya', 'TblBiaya.NoSPJ = TblSetoran.NoSPJ', 'inner');
		$this->db->where('NoPol', $nopol);
		$this->db->where('is_tujuan', 1);
		$query = $this->db->get('TblSetoran');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_op_awal_by_nopol($nopol) {
		$this->db->select("SUM(jumlah) AS jumlah");
		$this->db->join('TblBiaya', 'TblBiaya.NoSPJ = TblSetoran.NoSPJ', 'inner');
		$this->db->where('NoPol', $nopol);
		$this->db->where('is_tujuan', 0);
		$query = $this->db->get('TblSetoran');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_cabang_setoran_by_nopol($nopol) {
		$this->db->where('NoPol', $nopol);
		$query = $this->db->get('TblSetoran');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_laporan_sopir($tgl_awal, $tgl_akhir) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->group_by('driver');
		$this->db->select('driver,COUNT(driver) AS jumlah_driver');
		$this->db->where('tanggal BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_setoran_by_driver($driver) {
		$this->db->select("SUM(total_penumpang) AS total_penumpang,SUM(tiket_void) AS tiket_void,SUM(total_omset) AS total_omset,
                SUM(total_kapasitas) AS total_kapasitas");
		$this->db->where('driver', $driver);
		$query = $this->db->get('TblSetoran');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_distinct_hari_by_driver($driver) {
		$this->db->where('driver', $driver);
		$this->db->distinct();
		$this->db->select('tanggal');
		$query = $this->db->get('TblSetoran');
		if ($query->num_rows() > 0) {
			return $query->num_rows();
		} else {
			return FALSE;
		}
	}

	function get_jumlah_keberangkatan_by_driver($driver) {
		$this->db->where('driver', $driver);
		$this->db->select('COUNT(id) AS jumlah_keberangkatan');
		$query = $this->db->get('TblSetoran');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_coa() {
		return $this->db->get('TblCOA')->result_array();
	}

	function get_laporan_biaya($tgl_awal, $tgl_akhir, $idbiaya) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tgl_pembayaran BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		if ($idbiaya != 'SEMUA')
			$this->db->where('idbiaya', $idbiaya);
		$this->db->select('NoSPJ, cabang, SUM(jumlah) AS jumlah');
		$this->db->group_by('NoSPJ,cabang');
		return $this->db->get('TblBiaya')->result_array();
	}

	function get_jumlah_tujuan_laporan_biaya($tgl_awal, $tgl_akhir, $idbiaya) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tgl_pembayaran BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		if ($idbiaya != 'SEMUA')
			$this->db->where('idbiaya', $idbiaya);
		$this->db->select('SUM(jumlah) AS jumlah');
		$this->db->group_by('NoSPJ,cabang');
		$this->db->where('is_tujuan', 1);
		$query = $this->db->get('TblBiaya');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_jumlah_asal_laporan_biaya($tgl_awal, $tgl_akhir, $idbiaya) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tgl_pembayaran BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		if ($idbiaya != 'SEMUA')
			$this->db->where('idbiaya', $idbiaya);
		$this->db->select('SUM(jumlah) AS jumlah');
		$this->db->group_by('NoSPJ,cabang');
		$this->db->where('is_tujuan', 0);
		$query = $this->db->get('TblBiaya');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_setoran_user_by_biaya($nospj) {
		$this->db->join('TblSetoran', 'TblSetoran.NoSPJ=TblBiaya.NoSPJ', 'inner');
		$this->db->join('TbUser', 'TblBiaya.idkasir=TbUser.user_id', 'inner');
		$this->db->where('TblBiaya.NoSPJ', $nospj);
		$query = $this->db->get('TblBiaya');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_laporan_penjualan($tgl_awal, $tgl_akhir) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tglspj BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		return $this->db->get('TblSetoran')->result_array();
	}
	
	function get_laporan_penjualan_cabang($tgl_awal, $tgl_akhir,$cabang) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tglspj BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		$this->db->where('cabang_keberangkatan',$cabang);
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_kode_coa($asal, $tujuan) {
		$this->db->where('asal', $asal);
		$this->db->where('tujuan', $tujuan);
		$query = $this->db->get('TblCOAJurusan');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_laporan_penerimaan($tgl_awal, $tgl_akhir) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tanggal BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_laporan_penerimaan_cabang($tgl_awal, $tgl_akhir,$cabang) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tanggal BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		$this->db->where('cabang_keberangkatan',$cabang);
		return $this->db->get('TblSetoran')->result_array();
	}
	
	function get_biaya_langsung_by_SPJ($NoSPJ) {
		$this->db->select('SUM(jumlah) AS jumlah');
		$this->db->where('NoSPJ', $NoSPJ);
		$this->db->where('is_tujuan', 1);
		$query = $this->db->get('TblBiaya');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}
        
        function get_grafik_hari_produk_nopol($nopol,$bulan,$tahun)
        {
            $this->db->select('DAY(tanggal) AS tanggal, SUM(total_penumpang) AS total_penumpang, SUM(total_kapasitas) as total_kapasitas');
            $this->db->where('NoPol',$nopol);
            $this->db->where('MONTH(tanggal)',$bulan);
            $this->db->where('YEAR(tanggal)',$tahun);
            $this->db->group_by('DAY(tanggal)');
            return $this->db->get('TblSetoran')->result_array();
        }
        
        function get_grafik_bulan_produk_nopol($nopol,$tahun)
        {
            $this->db->select('MONTH(tanggal) AS tanggal, SUM(total_penumpang) AS total_penumpang, SUM(total_kapasitas) as total_kapasitas');
            $this->db->where('NoPol',$nopol);
            $this->db->where('YEAR(tanggal)',$tahun);
            $this->db->group_by('MONTH(tanggal)');
            return $this->db->get('TblSetoran')->result_array();
        }
        
        function get_grafik_hari_produk_driver($driver,$bulan,$tahun)
        {
            $this->db->select('DAY(tanggal) AS tanggal, SUM(total_penumpang) AS total_penumpang, SUM(total_kapasitas) as total_kapasitas');
            $this->db->where('driver',$driver);
            $this->db->where('MONTH(tanggal)',$bulan);
            $this->db->where('YEAR(tanggal)',$tahun);
            $this->db->group_by('DAY(tanggal)');
            return $this->db->get('TblSetoran')->result_array();
        }
        
        function get_grafik_bulan_produk_driver($driver,$tahun)
        {
            $this->db->select('MONTH(tanggal) AS tanggal, SUM(total_penumpang) AS total_penumpang, SUM(total_kapasitas) as total_kapasitas');
            $this->db->where('driver',$driver);
            $this->db->where('YEAR(tanggal)',$tahun);
            $this->db->group_by('MONTH(tanggal)');
            return $this->db->get('TblSetoran')->result_array();
        }

	function get_grafik_hari_jurusan_tujuan($asal, $tujuan, $bulan, $tahun) {
		$this->db->select('DAY(tanggal) AS tanggal, SUM(total_omset) AS total_omset, SUM(total_setoran) as total_setoran, SUM(total_laba) as total_laba, SUM(jumlah) as jumlah');
		$this->db->join('TblBiaya', 'TblBiaya.NoSPJ=TblSetoran.NoSPJ', 'inner');
		$this->db->where('asal', $asal);
		$this->db->where('tujuan', $tujuan);
		$this->db->where('MONTH(tanggal)', $bulan);
		$this->db->where('YEAR(tanggal)', $tahun);
		$this->db->where('is_tujuan', 1);
		$this->db->group_by('DAY(tanggal)');
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_grafik_hari_jurusan_asal($asal, $tujuan, $bulan, $tahun) {
		$this->db->select('SUM(jumlah) as jumlah');
		$this->db->join('TblBiaya', 'TblBiaya.NoSPJ=TblSetoran.NoSPJ', 'inner');
		$this->db->where('asal', $asal);
		$this->db->where('tujuan', $tujuan);
		$this->db->where('MONTH(tanggal)', $bulan);
		$this->db->where('YEAR(tanggal)', $tahun);
		$this->db->where('is_tujuan', 0);
		$this->db->group_by('DAY(tanggal)');
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_grafik_bulan_jurusan_tujuan($asal, $tujuan, $tahun) {
		$this->db->select('MONTH(tanggal) AS tanggal, SUM(total_omset) AS total_omset, SUM(total_setoran) as total_setoran, SUM(total_laba) as total_laba, SUM(jumlah) as jumlah');
		$this->db->join('TblBiaya', 'TblBiaya.NoSPJ=TblSetoran.NoSPJ', 'inner');
		$this->db->where('asal', $asal);
		$this->db->where('tujuan', $tujuan);
		$this->db->where('YEAR(tanggal)', $tahun);
		$this->db->where('is_tujuan', 1);
		$this->db->group_by('MONTH(tanggal)');
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_grafik_bulan_jurusan_asal($asal, $tujuan, $tahun) {
		$this->db->select('MONTH(tanggal) AS tanggal, SUM(total_omset) AS total_omset, SUM(total_setoran) as total_setoran, SUM(total_laba) as total_laba, SUM(jumlah) as jumlah');
		$this->db->join('TblBiaya', 'TblBiaya.NoSPJ=TblSetoran.NoSPJ', 'inner');
		$this->db->where('asal', $asal);
		$this->db->where('tujuan', $tujuan);
		$this->db->where('YEAR(tanggal)', $tahun);
		$this->db->where('is_tujuan', 0);
		$this->db->group_by('MONTH(tanggal)');
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_grafik_hari_produk_jurusan($asal, $tujuan, $bulan, $tahun) {
		$this->db->select('DAY(tanggal) AS tanggal, SUM(total_penumpang) AS total_penumpang, SUM(total_kapasitas) as total_kapasitas');
		$this->db->where('asal', $asal);
		$this->db->where('tujuan', $tujuan);
		$this->db->where('MONTH(tanggal)', $bulan);
		$this->db->where('YEAR(tanggal)', $tahun);
		$this->db->group_by('DAY(tanggal)');
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_grafik_bulan_produk_jurusan($asal, $tujuan, $tahun) {
		$this->db->select('MONTH(tanggal) AS tanggal, SUM(total_penumpang) AS total_penumpang, SUM(total_kapasitas) as total_kapasitas');
		$this->db->where('asal', $asal);
		$this->db->where('tujuan', $tujuan);
		$this->db->where('YEAR(tanggal)', $tahun);
		$this->db->group_by('MONTH(tanggal)');
		return $this->db->get('TblSetoran')->result_array();
	}

}

/** End of file */